#! /usr/bin/make -f

dbname := biosum
templdb := routing
dataDir := data
#create db directory
db:
	mkdir $@
data:
	mkdir $@

#create database
db/db: db
	python -c "import db;db.drop('${dbname}');db.create('${dbname}', template='${templdb}')"
	touch $@

.PHONY : struct
struct: db/db data

#BioSUM Data
db/getBiosum:
	python getBiosum.py
	mdb-schema  ${dataDir}/outPutDataForPeterv2.mdb postgres | psql ${dbname}
	mdb-export -q "'" -I postgres outPutDataForPeterv2.mdb FVS_Summary| psql biosum
	mdb-schema  ${dataDir}/scenario_results\ -\ HARVESTETCv2.mdb postgres | psql ${dbname}
	mdb-export -q "'" -I postgres ${dataDir}/scenario_results\ -\ HARVESTETCv2.mdb harvest_cost| psql ${dbname}
	mdb-export -q "'" -I postgres ${dataDir}/scenario_results\ -\ HARVESTETCv2.mdb tree_vol_val_by_species_diam_groups| psql ${dbname}
	touch $@

db/cost2:
	python cost2.py
	touch $@

#relation tabe for FVS data and FIA plots
db/relate:
	python fiafvs.py > $@

# Get FIA plot data
db/fiaGeoms:
	python fiaplots.py > $@


#Biosum volume over time
biosum_cost_vol_time.csv:
	psql -d ${dbname} -c "copy (select \"StandID\",p.cn fia_cn,p.plot,\"CaseID\",\"Year\",\"TCuFt\",\"MCuFt\",\"RTpa\",\"RTCuFt\",\"RMCuFt\", acres,hc.complete_cpa, hc.harvest_cpa, hc.water_barring_roads_cpa as road_maint_cpa, hc.brush_cutting_cpa  from \"FVS_Summary\" join cond c on(\"StandID\"=biosum_cond_id) join ca_cond co on (c.cn=co.cn) join ca_plot p on (co.plt_cn=p.cn) join harvest_costs hc on(\"StandID\"=hc.biosum_cond_id) join hcost2 hc2 (on \"StandID\"=hc2.biosum_cond_id)) to '/tmp/$@' with csv header;"
